﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelTools = Microsoft.Office.Tools.Excel;
using SqlTypeToFramerworkType;
using MyDataProvider;
using Cfg;
using TableInfo;

namespace ExcelDbAddIn
{
    public partial class MyRibbon
    {
        Dictionary<string, TableInfo.TableInfo> _loNameToTableInfo;
        Observer.ExcelObserver _excelObserver;

        ColumnsInformation.ColumnsInfo columnsInfo;
        string[] columnsForShow;
        
        int entriesPerPage = 0;
        int startRow = 0;
        int endRow;
        int nPages = 0;
        string initPosOfInsertion;

        SqlConnectionStringBuilder sqlConnString;
        SqlConnectionConfig _globalConfig = new SqlConnectionConfig()
        {
            DataSource = "SQL-SERVER",
            Auth = new MSSQL.Authorization()
            { 
                Type = MSSQL.Authorization.AuthorizationType.SQLAuth, 
                UserId = "sa",
                Password = "1000000"
            }
        };
        DataSet dataSet;

        string currLOName;
        int cntLO = 0;
        
        private void Ribbon_Load(object sender, RibbonUIEventArgs e)
        {
            dropDownEntriesPerList.SelectedItemIndex = 0;
            entriesPerPage = Convert.ToInt32(dropDownEntriesPerList.SelectedItem.Label);
            nPages = 1;
            startRow = 1;
            endRow = entriesPerPage;
            List<RibbonButton> navigateButtons = new List<RibbonButton>();
            navigateButtons.Add(btnFirstPage);
            navigateButtons.Add(btnPreviousPage);
            navigateButtons.Add(btnNextPage);
            navigateButtons.Add(btnLastPage);

            Dictionary<string, RibbonLabel> tableNameToLabel = new Dictionary<string,RibbonLabel>();
            tableNameToLabel.Add("NumEntries", lblNumEntries);
            tableNameToLabel.Add("CurPage", lblCurPage);
            tableNameToLabel.Add("TableName", lblTableName);
            _excelObserver = new Observer.ExcelObserver(Globals.ThisAddIn.Application, btnSetView, navigateButtons, tableNameToLabel, dropDownEntriesPerList);
            _loNameToTableInfo = Globals.ThisAddIn._loNameToTableInfo;
        }
        private void Ribbon_SheetChange(object sheet, Excel.Range Target)
        {
            MessageBox.Show((sheet as ExcelTools.Worksheet).Name);
        }
        private void ThisWorkbook_SheetActivate(object Sh)
        {
            Excel.Worksheet sheet = (Excel.Worksheet)Sh;
            MessageBox.Show(sheet.Name + " Activated");
        }

        private ExcelTools.Worksheet GetActiveWorksheet()
        {
            return Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
        }
        private void buttonMapBD_Click(object sender, RibbonControlEventArgs e)
        {
            FormSelectingDatabase formSelectingDatabase = new FormSelectingDatabase();
            formSelectingDatabase.SetConfig(_globalConfig);
            DialogResult dlgRes  = formSelectingDatabase.ShowDialog();
            if (dlgRes == DialogResult.Cancel)
                return;
            SqlConnectionConfig config = formSelectingDatabase.GetConfig();
            columnsForShow = formSelectingDatabase.columnsForShow;
            columnsInfo = formSelectingDatabase.columnsInfo;

            bool isBadChoice = true;
            object objRange;
            try
            {
                while (isBadChoice)
                {
                    isBadChoice = false;
                    objRange = Globals.ThisAddIn.Application.InputBox("Выберите ячейку, с которой вставиться таблица из БД",
                                                    Type.Missing, Globals.ThisAddIn.Application.ActiveCell.Address, Type.Missing, Type.Missing,
                                                    Type.Missing, Type.Missing, 8);
                    Excel.Range rng = objRange as Excel.Range;
                    if (rng == null)
                        return;
                    initPosOfInsertion = rng.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing);
                    foreach (var el in _loNameToTableInfo)
                    {
                        if (rng.Worksheet.Name == el.Value._listObjectMaker._worksheet.Name)
                        {
                            Excel.Range result = Globals.ThisAddIn.Application.Intersect(rng, el.Value._listObjectMaker._listObject.Range);
                            if (result != null)
                            {
                                MessageBox.Show("Выбранная ячейка находится внутри таблицы. Пожалуйста выберите другую ячейку вне таблицы");
                                isBadChoice = true;
                                break;
                            }
                        }
                    }
                }
                int cntEntries = MSSQL.GetNumOfEntries(sqlConnString, config.Database, config.Schema, config.Table);
                var currSheet = GetActiveWorksheet();
                sqlConnString = MSSQL.GetConnectionString(config);
                // Выбрали "Отобразить все строки таблицы"
                if (startRow == 1 && endRow == -1)
                    endRow = cntEntries;
                else
                    btnFirstPage.Enabled = btnNextPage.Enabled = btnPreviousPage.Enabled = btnLastPage.Enabled = true;
                dataSet = MSSQL.GetTableContentAsDataset(sqlConnString, config.Database, config.Schema, config.Table, columnsInfo, columnsForShow, startRow, endRow);
                ListObjectMaker.MyListObject loMaker = new ListObjectMaker.MyListObject(currSheet, dataSet.Tables[0], initPosOfInsertion, config, startRow, endRow, cntLO);
                loMaker.GenerateExcelTable();
                cntLO++;
                TableInfo.TableInfo tabInfo = new TableInfo.TableInfo(config, columnsInfo, columnsForShow, dataSet.Tables[0], startRow, endRow, loMaker, initPosOfInsertion);
                tabInfo._cntEntries = cntEntries;
                tabInfo._entriesPerPage = entriesPerPage;
                tabInfo._nPage = 1;
                lblCurPage.Label = tabInfo._nPage.ToString();
                _loNameToTableInfo[loMaker.Name] = tabInfo;
                _excelObserver.AddListObject(currSheet.Name, loMaker._listObject);
                this.RibbonUI.ActivateTabMso("TabAddIns");
                
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        private void Refresh()
        {
            try
            {
                TableInfo.TableInfo tabInfo = _loNameToTableInfo[lblTableName.Label];
                int startRow = tabInfo._startRow;
                int endRow = tabInfo._endRow;
                int cntEntries = tabInfo._cntEntries;
                // Выбрали "Отобразить все строки таблицы"
                if (startRow == 1 && endRow == -1)
                    endRow = cntEntries;
                else
                    btnFirstPage.Enabled = btnNextPage.Enabled = btnPreviousPage.Enabled = btnLastPage.Enabled = true;                
                Cfg.SqlConnectionConfig config = tabInfo._config;
                sqlConnString = MSSQL.GetConnectionString(config);
                dataSet = MSSQL.GetTableContentAsDataset(
                    sqlConnString, config.Database, config.Schema, config.Table, 
                    tabInfo._columnsInfo, tabInfo._columnsForShow, startRow, endRow);
                tabInfo._listObjectMaker.SetDataBinding(dataSet.Tables[0]);
                tabInfo._table = dataSet.Tables[0];
                tabInfo._listObjectMaker.AutoFit();
                /*
                ListObjectMaker.ListObjectMaker myWorker = new ListObjectMaker.ListObjectMaker(GetActiveWorksheet(), dataSet.Tables[0], selectedRange, _config, startRow, endRow, cntLO);
                myWorker.GenerateExcelTable();                            
                */
                this.RibbonUI.ActivateTabMso("TabAddIns");
            }
            catch (Exception ex)
            {
                Globals.ThisAddIn.Application.ScreenUpdating = true;
                MessageBox.Show(ex.Message);
                throw ex;
            }
        }
 
        private Excel.Worksheet CreateAndActiveWorkbook(Microsoft.Office.Interop.Excel.Application application)
        {
            Excel.Workbook workbook = application.Workbooks.Add(Type.Missing);
            Excel.Worksheet worksheet = workbook.ActiveSheet;
            return worksheet; 
        }
        private void buttonUpdate_Click(object sender, RibbonControlEventArgs e)
        {
           
        }

        private void btnConfigConnection_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                FormConnection connDialog = new FormConnection();
                connDialog.SetConfig(_globalConfig);
                if (connDialog.ShowDialog() != DialogResult.OK)
                    return;
                _globalConfig = connDialog.GetConfig();
                sqlConnString = MSSQL.GetConnectionString(_globalConfig);
                buttonMapBD.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw ex;
            }                
        }          

        private void btnFirstPage_Click(object sender, RibbonControlEventArgs e)
        {
            TableInfo.TableInfo tabInfo = _loNameToTableInfo[lblTableName.Label];
            tabInfo._startRow = 1;
            tabInfo._endRow = tabInfo._entriesPerPage;
            tabInfo._nPage = 1;
            lblCurPage.Label = "1";
            Refresh();
            //GenerateExcelTable(worksheet, dataSetSql);
        }

        private void btnPreviousPabe_Click(object sender, RibbonControlEventArgs e)
        {
            TableInfo.TableInfo tabInfo = _loNameToTableInfo[lblTableName.Label];
            tabInfo._startRow -= tabInfo._entriesPerPage;
            if (tabInfo._startRow < 1)
            {
                tabInfo._startRow = 1;
                tabInfo._endRow = tabInfo._entriesPerPage;
                tabInfo._nPage = 1;
                lblCurPage.Label = tabInfo._nPage.ToString();
                
                return;
            }
            tabInfo._endRow -= tabInfo._entriesPerPage;
            tabInfo._nPage--;
            if (tabInfo._nPage <= 1)
                tabInfo._nPage = 1;
            lblCurPage.Label = tabInfo._nPage.ToString();
            Refresh();
            //GenerateExcelTable(worksheet, dataSetSql);
        }

        private void btnNextPage_Click(object sender, RibbonControlEventArgs e)
        {
            TableInfo.TableInfo tabInfo = _loNameToTableInfo[lblTableName.Label];
            if (tabInfo._startRow + tabInfo._entriesPerPage + 1 > tabInfo._cntEntries)
                return;
            tabInfo._startRow += tabInfo._entriesPerPage;
            tabInfo._endRow += tabInfo._entriesPerPage;
            tabInfo._nPage++;
            lblCurPage.Label = tabInfo._nPage.ToString();
            Refresh();
            //GenerateExcelTable(worksheet, dataSetSql);
        }

        private void btnLastPage_Click(object sender, RibbonControlEventArgs e)
        {
            TableInfo.TableInfo tabInfo = _loNameToTableInfo[lblTableName.Label];
            tabInfo._endRow = tabInfo._cntEntries;
            tabInfo._startRow = tabInfo._endRow - tabInfo._entriesPerPage + 1;
            if (tabInfo._startRow <= 1)
                tabInfo._startRow = 1;
            tabInfo._nPage = tabInfo._cntEntries / tabInfo._entriesPerPage;
            if (tabInfo._cntEntries % tabInfo._entriesPerPage != 0)
                tabInfo._nPage++;
            lblCurPage.Label = tabInfo._nPage.ToString();
            // nPages = lastPage;
            Refresh();
        }

        private void dropDownEntriesPerList_SelectionChanged(object sender, RibbonControlEventArgs e)
        {
            TableInfo.TableInfo currSelectedTabInfo = _loNameToTableInfo[lblTableName.Label];
            string strEntriesPerPage = dropDownEntriesPerList.SelectedItem.Label;
            if (strEntriesPerPage == "Все")
            {
                currSelectedTabInfo._startRow = 1;
                currSelectedTabInfo._endRow = -1;
                currSelectedTabInfo._nPage = 1;
                lblCurPage.Label = nPages.ToString();
                lblCurPage.Label = nPages.ToString();
                btnFirstPage.Enabled = btnNextPage.Enabled = btnPreviousPage.Enabled = btnLastPage.Enabled = false;
                Refresh();
                return;
            }
            currSelectedTabInfo._entriesPerPage = Convert.ToInt32(dropDownEntriesPerList.SelectedItem.Label);
            currSelectedTabInfo._startRow = 1;
            currSelectedTabInfo._endRow = currSelectedTabInfo._entriesPerPage;
            Refresh();
        }       

        private void btnSetView_Click(object sender, RibbonControlEventArgs e)
        {
            _excelObserver.StopObserv();
            TableInfo.TableInfo currSelectedTabInfo = _loNameToTableInfo[lblTableName.Label];
            // Получили имя схемы и название таблицы.
            string database = currSelectedTabInfo._config.Database;
            string schema = currSelectedTabInfo._config.Schema;
            string table = currSelectedTabInfo._config.Table;
            SqlConnectionStringBuilder sqlConnString = MSSQL.GetConnectionString(currSelectedTabInfo._config);
            ColumnsInformation.ColumnsInfo columnsInfo = MSSQL.GetColumnsSchemaByQueryToSchema(sqlConnString, database, schema, table);
            // Создаем форму "Настройка отображения"
            FormView formView = new FormView();
            // Указываем той форме информацию о столбцах
            formView.columnsInfo = columnsInfo;
            // Указываем форме колонки, которые выбраны для отображения
            formView.columnsForShow = currSelectedTabInfo._columnsForShow;
            // Показываем форму.
            DialogResult res = formView.ShowDialog();
            if (res != DialogResult.OK)
                return;
            int cntOfShowedColumns = currSelectedTabInfo._columnsForShow.Count();
            int cntOfNewColumns = formView.columnsForShow.Count();
            currSelectedTabInfo._columnsForShow = formView.columnsForShow;
            int diff = cntOfShowedColumns - cntOfNewColumns;

            int startRow = currSelectedTabInfo._startRow;
            int endRow = currSelectedTabInfo._endRow;
            int cntEntries = currSelectedTabInfo._cntEntries;
            // Выбрали "Отобразить все строки таблицы"
            if (startRow == 1 && endRow == -1)
                endRow = cntEntries;
            else
                btnFirstPage.Enabled = btnNextPage.Enabled = btnPreviousPage.Enabled = btnLastPage.Enabled = true;

            Cfg.SqlConnectionConfig config = currSelectedTabInfo._config;
            sqlConnString = MSSQL.GetConnectionString(config);
            dataSet = MSSQL.GetTableContentAsDataset(
                sqlConnString, config.Database, config.Schema, config.Table,
                currSelectedTabInfo._columnsInfo, currSelectedTabInfo._columnsForShow, startRow, endRow);

            string address = currSelectedTabInfo._initPosOfInsertion;
            string currSheetName = GetActiveWorksheet().Name;

            // Находим все ListObject's(таблицы), стоящие справа от текущей и находящиеся на данном листе.
            List<TableInfo.TableInfo> rightTables = GetListOfTablesStandingMoreRight(currSheetName, lblTableName.Label, dataSet.Tables[0], address);
            List<ExcelCoordinate> listOfCoordinate = new List<ExcelCoordinate>();
            
            foreach (var tab in rightTables)
            {
                System.Diagnostics.Debug.WriteLine(tab._listObjectMaker.Name);

                int row, col;
                ListObjectMaker.MyListObject.GetRowAndColumn(tab._initPosOfInsertion, out row, out col);
                // newRow без -1 т.к. одну строку занимает заголовок.
                int newRow = row + tab._table.Rows.Count;
                int newCol = col + tab._columnsForShow.Count() - 1;
               
                string addrTopLeft = tab._listObjectMaker._worksheet.Cells[row, col - diff].Address;
                string addrBottomRight = tab._listObjectMaker._worksheet.Cells[newRow, newCol - diff].Address;                
                listOfCoordinate.Add(new ExcelCoordinate()
                { 
                    AddrTopLeft = addrTopLeft, 
                    AddrBottomRight = addrBottomRight 
                });

                Excel.Range rng = tab._listObjectMaker._worksheet.Range[addrTopLeft, addrBottomRight];       
                string addr = rng.Address[Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing];
                tab._initPosOfInsertion = addr.Substring(0, addr.IndexOf(':'));
                System.Diagnostics.Debug.WriteLine("Table {0} new size: {1}", tab._listObjectMaker.Name, addr);

                _excelObserver.RemoveListObject(currSheetName, tab._listObjectMaker.Name);
                tab._listObjectMaker.Delete();               
            }            
            currSelectedTabInfo._listObjectMaker.SetDataBinding(dataSet.Tables[0]);
            currSelectedTabInfo._table = dataSet.Tables[0];
            currSelectedTabInfo._listObjectMaker.AutoFit();
            int k = 0;
            foreach (var tab in rightTables)
            {
                /*
                string addr = string.Format("{0}:{1}", listOfCoordinate[k].AddrTopLeft, listOfCoordinate[k].AddrBottomRight);
                System.Diagnostics.Debug.WriteLine("Table {0} new size: {1}", tab._listObjectMaker.Name, addr);
                */
                ExcelTools.ListObject listObject = tab._listObjectMaker._worksheet.Controls.AddListObject(tab._listObjectMaker._worksheet.Range[listOfCoordinate[k].AddrTopLeft, listOfCoordinate[k].AddrBottomRight],
                                                                                                          tab._listObjectMaker.Name);
                listObject.AutoSetDataBoundColumnHeaders = true;
                listObject.SetDataBinding(tab._table);
                tab._listObjectMaker.SetListObject(listObject);
                tab._listObjectMaker.AutoFit();
                _excelObserver.AddListObject(currSheetName, listObject);
                k++;
            }
            _excelObserver.StartObserv();
            this.RibbonUI.ActivateTabMso("TabAddIns");
        }
        
        private List<TableInfo.TableInfo> GetListOfTablesStandingMoreRight(string sheetName, string tableName, DataTable table, string address)
        {
            List<TableInfo.TableInfo> result = new List<TableInfo.TableInfo>();
            int currStartRow, currStartCol, currEndRow, currEndCol;
            ListObjectMaker.MyListObject.GetRowAndColumn(address, out currStartRow, out currStartCol);
            currEndRow = currStartRow + table.Rows.Count;
            currEndCol = currStartCol + table.Columns.Count - 1;
            foreach (var tab in _loNameToTableInfo)
            {
                if (tab.Key == tableName)
                    continue;
                if (tab.Value._listObjectMaker._worksheet.Name != sheetName)
                    continue;
                int nRows = tab.Value._table.Rows.Count;
                int nCols = tab.Value._columnsForShow.Count();
                int startRow, startCol, endRow, endCol;
                ListObjectMaker.MyListObject.GetRowAndColumn(tab.Value._initPosOfInsertion, out startRow, out startCol);
                endRow = startRow + nRows;
                endCol = startCol + nCols - 1;
                if (startCol > currStartCol && ((startRow >= currStartRow && startRow <= currEndRow)
                                        || (endRow >= currStartRow && endRow <= currEndRow)
                                        || (endRow == currStartRow) || (startRow == currEndRow)
                                        || (startRow <= currStartRow && endRow >= currEndRow)))
                    result.Add(tab.Value);
            }
            result.Sort(CompareTablesByPosition);
            return result;
        }
        private int CompareTablesByPosition(TableInfo.TableInfo tab1, TableInfo.TableInfo tab2)
        {
            if (tab1 == null && tab2 != null)
                return 1;
            if (tab1 != null && tab2 == null)
                return -1;
            if (tab1 == null && tab2 == null)
                return 0;
            int r1, r2, c1, c2;
            ListObjectMaker.MyListObject.GetRowAndColumn(tab1._initPosOfInsertion, out r1, out c1);
            ListObjectMaker.MyListObject.GetRowAndColumn(tab2._initPosOfInsertion, out r2, out c2);
            if (c1 > c2)
                return -1;
            else
                if (c1 < c2)
                    return 1;
                else
                    return 0;
        }
    }
}
